ajMatch function
Available since AlchemyJ v4.3.1 for AlchemyJ Excel Library, AlchemyJ Community Edition, AlchemyJ Enterprise Edition
Description
The ajMatch function returns the position of an item in an array.
Syntax
ajMatch(lookup_value, lookup_array, [match_type], [cache],[return_first_match])
| Argument Name | Argument Type | Description |
|---|---|---|
| lookup_value (required) | Variant | The value to match in lookup_array. |
| lookup_array (required) | Variant | A range of cells or an array reference. |
| match_type (optional) | Integer | 1 = exact or next smallest. 0 = exact match and always return the first positon when multiple matchs found. -1 = exact or next largest. The default value is 1. The array has to be sorted when match type is 1 or -1. |
| cache (optional) | Boolean | TRUE means to cache the lookup_array range. Subsequent ajMatch call to the same range will get the lookup_array from cache instead. With the cache enable, the performance will be much better than Excel Match() when the same range is used for matches many times in a function point. FALSE means will not use cache. The default value is False. |
| return_first_match (optional) | Boolean | TRUE means to return the position of the match with the first position in sequence. FALSE means to return the position of the first found match. The default value is FALSE. This option only works when match type is not 0. |
The function will return:
1) Return Value: Position of the lookup value
2) Return Type: Number
Example
Example 1
In this sample, we lookup "HKEX" in the array from B2 to B9. ajMatch is using dichotomy to sarch the array when the match type is not 0. In this sample, it is using the default match type 1, thus it will return 4 as the first found record as the result.
Example 2
Similar with example 1, but has changed return_first_match to TRUE. Then it will search the array in sequence, and the first match positon is 3.
Error Scenarios
It will return #VALUE! when missing any required parameter or mismatch parameter type.